﻿#include "dbservice.h"
#include <QSqlQuery>
#include <QSqlError>
#include "alarmreq.h"
#include "TcpConnectHandler.h"
#include "syscfg.h"
#include "commonresp.h"
#include "AppMonitorTaskManager.h"


static const QString SYS_SETTING_TABLE_NAME("sys_setting");
static const QString DB_VERSION_KEY("db_version_key");
DBService gDBService;


int sendAppMonitorRestartLogAndSaveToDb(AppMonitorLog &log, QString &returnInfo)
{
    QString alarmData(log.appName + " do " + log.action + " at " + sayTime(QDateTime::currentDateTime()));
    AlarmReq *req = new AlarmReq(AlarmReqType_AppRestart, log.appName.toStdString(), alarmData.toStdString());


    log.isSuccess = (f_success == sendMsgWithCommonResp(req, WATCHDOG_SERVER_IP, WATCHDOG_SERVER_PORT, log.failReason));

    if(gDBService.saveOneAppMonitorLog(log) == f_success)
    {
        LOG_INFO("saveOneAppMonitorLog to db success");
        emit gAppMonitorTaskManager.appRestarted();
    }
    else
    {
        LOG_ERROR("saveOneAppMonitorLog to db failed");
    }

    return log.isSuccess;
}

DBService::DBService()
{

}

int DBService::openDB(QSqlDatabase & dataBase)
{
    static int index = 0;
    openDBLock.lock();

    index++;

    LOG_INFO("try to open sqlite with db name:%s, user name:%s, passwd:%s", SQL_DB_NAME, SQL_USER_NAME, SQL_USER_PASSWD);
    dataBase = QSqlDatabase::addDatabase("QSQLITE", "sqlitedb_" + int2QStr(index));
    dataBase.setDatabaseName(SQL_DB_NAME);
    dataBase.setUserName(SQL_USER_NAME);
    dataBase.setPassword(SQL_USER_PASSWD);
    bool openOk =  dataBase.open();
    if(openOk)
    {
        LOG_INFO("open db:%s success", QStr2CStr(dataBase.connectionName()));
        openDBLock.unlock();
        return f_success;

    }
    else
    {
        LOG_ERROR("open db failed");
        openDBLock.unlock();
        return f_fail;
    }
}


int DBService::checkAndUpdateDB()
{
    bool sysSettingTableExist = false;
    if(isTableExist(SYS_SETTING_TABLE_NAME, sysSettingTableExist) == f_success)
    {
        if(!sysSettingTableExist)
        {
            LOG_INFO("sys_setting table not existed, will try to create it and set db version to 0.0f");
            if(createSysSettingTable() == f_success && saveDBVersion(0) == f_success)
            {
                LOG_INFO("create sys_setting and save db version to 0 success");
            }
            else
            {
                LOG_ERROR("create sys setting or save db version failed, check and update db failed");
                return f_fail;
            }
        }
        return doUpdateDB();
    }
    else
    {
        LOG_ERROR("check sys setting table's exist failed, check and update db failed");
        return f_fail;
    }
}
int DBService::doUpdateDB()
{
    LOG_INFO("doUpdateDB");
    int dbVersion = 0;
    if(getDBVersion(dbVersion) == f_success)
    {
        if(dbVersion < SQL_DB_VERSION)
        {
            return doUpdateDBFrom5_0To6_0();
        }
        else
        {
            LOG_INFO("db version:%d same to current db version ,no need to update db", dbVersion);
            return f_success;
        }
    }
    else
    {
        LOG_ERROR("try to get db version failed, do update db failed");
        return f_fail;
    }
}

int DBService::getSysSettingValue(const QString &key, QString & value)
{
    QString sql = "select value from " + SYS_SETTING_TABLE_NAME + " where key = '" + key + "';";

    return queryForString(sql, value);
}

int DBService::deleteSysSettingValue(const QString &key)
{
     QString sql = "delete from " + SYS_SETTING_TABLE_NAME + " where key = '" + key + "';";
     return execSql(sql);
}

int DBService::insertSysSettingValue(const QString &key, const QString &value)
{
    QString sql = "insert into " + SYS_SETTING_TABLE_NAME + " values('" + key + "','" + value + "');";
    return execSql(sql);
}

int DBService::deleteOneAppMonitor(AppMonitor &monitor)
{
    QString sql = "delete from app_monitor where id = " + int2QStr(monitor.id);
    return execSql(sql);
}


int DBService::saveOneAppMonitorLog(AppMonitorLog &log)
{
    /*
     *             QString sql = "CREATE TABLE  IF NOT EXISTS  app_monitor_log (\
                     'id' INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,\
                     'name' VARCHAR(150)  NOT NULL,\
                     'action' VARCHAR(100)  NOT NULL,\
                     'time' VARCHAR(60)  NOT NULL\
                    );";
*/
    QSqlDatabase dataBase;
    openDB(dataBase);
	
    QSqlQuery sql_query(dataBase);
    QString sql = "insert into app_monitor_log(name,action,time,is_success,fail_reason) values (?,?,?,?,?);";
    sql_query.prepare(sql);
    sql_query.bindValue(0,log.appName);
    sql_query.bindValue(1,log.action);
    sql_query.bindValue(2,log.time);
    sql_query.bindValue(3,log.isSuccess);
    sql_query.bindValue(4,log.failReason);
    if(sql_query.exec())
    {
        LOG_INFO("try to save one app_monitor_log success");


        log.id = sql_query.lastInsertId().toInt();


        dataBase.close();
        return f_success;
    }
    else
    {
        LOG_ERROR("try to save one app_monitor_log failed,error:%s", QStr2CStr(sql_query.lastError().text()));
        dataBase.close();
        return f_fail;
    }
}

int DBService::loadMonitorLogs(list<AppMonitorLog *> & logs, QString *monitorName, QString *timeStart, QString *timeEnd, QString *logState, int maxCount)
{
    SafeClearListAndDelEle(AppMonitorLog, logs);

    QSqlDatabase dataBase;
    openDB(dataBase);
    QSqlQuery sql_query(dataBase);
	

	
    QString sql = "select id, name, action, time, is_success, fail_reason from app_monitor_log where 1=1";
    if(monitorName) sql += " and name = '" + *monitorName + "'";
    if(timeStart) sql += " and time >= '" + *timeStart + "'";
    if(timeEnd) sql += " and time <= '" + *timeEnd + "'";
    if(logState) sql += " and is_success = '" + *logState + "'";
    sql += " order by time desc ";
    if(maxCount > 0) sql += " limit 0, " + int2QStr(maxCount);

    int iRet = f_fail;

    sql_query.prepare(sql);
    if(!sql_query.exec())
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
    }
    else
    {
        while(sql_query.next())
        {
            AppMonitorLog * log = new AppMonitorLog();
            log->id = sql_query.value(0).toInt();
            log->appName = sql_query.value(1).toString();
            log->action = sql_query.value(2).toString();
            log->time = sql_query.value(3).toString();
            log->isSuccess = sql_query.value(4).toInt();
            log->failReason = sql_query.value(5).toString();
            logs.push_back(log);
        }

        iRet = f_success;
        LOG_INFO("load monitor logs from db success with sql:%s, logs count:%d",QStr2CStr(sql), logs.size());
    }
	
	dataBase.close();
    return iRet;
}


int DBService::loadMonitorLogsCount(int &count, QString *monitorName, QString *timeStart, QString *timeEnd, QString *logState)
{
    QString sql = "select count(1) from app_monitor_log where 1=1";
    if(monitorName) sql += " and name = '" + *monitorName + "'";
    if(timeStart) sql += " and time >= '" + *timeStart + "'";
    if(timeEnd) sql += " and time <= '" + *timeEnd + "'";
    if(logState) sql += " and is_success = " + *logState + "";

    return queryForInt(sql, count);
}

int DBService::deleteMonitorLogs( QString *monitorName, QString *timeStart, QString *timeEnd, QString *logState)
{
    QString sql = "delete from app_monitor_log where 1=1";
    if(monitorName) sql += " and name = '" + *monitorName + "'";
    if(timeStart) sql += " and time >= '" + *timeStart + "'";
    if(timeEnd) sql += " and time <= '" + *timeEnd + "'";
    if(logState) sql += " and is_success = " + *logState + "";

    return execSql(sql);
}

int DBService::getMonitorNames(list<QString> &names)
{
    int iRet = f_success;
    QSqlDatabase dataBase;
    openDB(dataBase);
    QSqlQuery sql_query(dataBase);
	

	
    names.clear();
    QString sql = "select distinct name from app_monitor_log;";
    sql_query.prepare(sql);
	
	
    if(!sql_query.exec())
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
    }
    else
    {
        while(sql_query.next())
        {
            names.push_back(sql_query.value(0).toString());

        }

        iRet = f_success;
        LOG_INFO("load monitor distinct  names from db success, sql:%s, names count:%d",QStr2CStr(sql), names.size());
    }
	
	dataBase.close();
    return iRet;

}


int DBService::saveOneAppMonitor(AppMonitor &monitor)
{
    QSqlDatabase dataBase;
    openDB(dataBase);
    QSqlQuery sql_query(dataBase);

    bool isInsert = (monitor.id < 0);
    if(isInsert)
    {
        int i = 0;
        QString sql = "insert into app_monitor(name,start_cmd,type,cfg_info,interval, stop, max_mem_kb) values (?,?,?,?,?,?,?);";
        sql_query.prepare(sql);
        sql_query.bindValue(i++,monitor.name);
        sql_query.bindValue(i++,monitor.startCmd);
        sql_query.bindValue(i++,monitor.type);
        sql_query.bindValue(i++,monitor.cfgInfo);
        sql_query.bindValue(i++,monitor.checkIntervalSeconds);
        sql_query.bindValue(i++,monitor.stopFlag);
        sql_query.bindValue(i++,monitor.maxMemLimitKB);
    }
    else
    {
        int i = 0;
        QString sql = "update app_monitor set name=?, start_cmd=?, type=?, cfg_info=?, interval=?, stop=?, max_mem_kb=? where id = ?;";
        sql_query.prepare(sql);
        sql_query.bindValue(i++,monitor.name);
        sql_query.bindValue(i++,monitor.startCmd);
        sql_query.bindValue(i++,monitor.type);
        sql_query.bindValue(i++,monitor.cfgInfo);
        sql_query.bindValue(i++,monitor.checkIntervalSeconds);
        sql_query.bindValue(i++,monitor.stopFlag);
        sql_query.bindValue(i++,monitor.maxMemLimitKB);
        sql_query.bindValue(i++,(unsigned int)monitor.id);

    }


    if(sql_query.exec())
    {
        LOG_INFO("try to save one app_monitor success");

        if(isInsert)
        {
            monitor.id = sql_query.lastInsertId().toInt();
        }
		
		dataBase.close();

        return f_success;
    }
    else
    {
        LOG_ERROR("try to save one app_monitor failed,error:%s", QStr2CStr(sql_query.lastError().text()));
		dataBase.close();
        return f_fail;
    }

}

int DBService::loadAllAppMonitor(list<AppMonitor *> & monitors)
{
    int iRet = f_success;
    QString sql = "select id,name,start_cmd,type,cfg_info,interval,stop,max_mem_kb from app_monitor;";

    //id,QString & name, QString & startCmd, MonitorType monitorType, QString &cfgInfo, int checkIntervalSeconds

    QSqlDatabase dataBase;
    openDB(dataBase);
    QSqlQuery sql_query(dataBase);
	

    
	sql_query.prepare(sql);
    if(!sql_query.exec())
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
		dataBase.close();
    }
    else
    {

        iRet = f_success;
        while(sql_query.next())
        {
             AppMonitor * monitor = new AppMonitor(sql_query.value(0).toInt(),
                                                   sql_query.value(1).toString(),
                                                   sql_query.value(2).toString(),
                                                   sql_query.value(3).toInt(),
                                                   sql_query.value(4).toString(),
                                                   sql_query.value(5).toInt(),
                                                   sql_query.value(7).toInt());
            monitor->stopFlag = sql_query.value(6).toInt();
            monitors.push_back(monitor);
        }

         LOG_INFO("try to loadAllAppMonitor success with sql:%s success, result count:%d", QStr2CStr(sql),monitors.size());
		 dataBase.close();

    }

    return iRet;
}

int DBService::loadSysMonitorLogs(list<SysMonitorLog >&logs,QString *kind, QString *timeStart, QString *timeEnd, bool* isSuccess,int maxCount)
{
    int iRet = f_fail;
    logs.clear();
    QString sql = "select id,kind,action,time,is_success,fail_reason from sys_monitor_log where 1=1";
    if(kind) sql += " and kind = '" + *kind + "'";
    if(timeStart) sql += " and time >= '" + *timeStart + "'";
    if(timeEnd) sql += " and time <= '" + *timeEnd + "'";
    if(isSuccess) sql += " and is_success = " + int2QStr(boolTo0Or1(*isSuccess));
    sql += " order by time desc";
    if(maxCount > 0) sql += " limit 0, " + int2QStr(maxCount);

    QSqlDatabase dataBase;
    openDB(dataBase);

    QSqlQuery sql_query(dataBase);
	

	
    sql_query.prepare(sql);
    if(!sql_query.exec())
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
    }
    else
    {

        iRet = f_success;
        while(sql_query.next())
        {
             SysMonitorLog log;
             log.id = sql_query.value(0).toInt();
             log.kind = sql_query.value(1).toString();
             log.action = sql_query.value(2).toString();
             log.time = sql_query.value(3).toString();
             log.isSuccess = (sql_query.value(4).toInt()!=0);
             log.failReason = sql_query.value(5).toString();
             logs.push_back(log);
        }

         LOG_INFO("try to loadSysMonitorLogs success with sql:%s success, result count:%d", QStr2CStr(sql),logs.size());

    }
	dataBase.close();
    return iRet;
}

int DBService::saveOneSysMonitorLog(SysMonitorLog &log)
{
    QSqlDatabase dataBase;
    openDB(dataBase);
    QSqlQuery sql_query(dataBase);

    bool isInsert = (log.id < 0);
    if(isInsert)
    {
        int i = 0;
        QString sql = "insert into sys_monitor_log(kind,action,time,is_success,fail_reason) values (?,?,?,?,?);";
        sql_query.prepare(sql);
        sql_query.bindValue(i++,log.kind);
        sql_query.bindValue(i++,log.action);
        sql_query.bindValue(i++,log.time);
        sql_query.bindValue(i++,log.isSuccess);
        sql_query.bindValue(i++,log.failReason);
    }
    else
    {
        int i = 0;
        QString sql = "update sys_monitor_log set kind=?, action=?, time=?, is_success=?, fail_reason=? where id = ?;";
        sql_query.prepare(sql);
        sql_query.bindValue(i++,log.kind);
        sql_query.bindValue(i++,log.action);
        sql_query.bindValue(i++,log.time);
        sql_query.bindValue(i++,log.isSuccess);
        sql_query.bindValue(i++,log.failReason);
        sql_query.bindValue(i++,(unsigned int)log.id);

    }


    if(sql_query.exec())
    {
        LOG_INFO("try to save one sys_monitor_log success");

        if(isInsert)
        {
            log.id = sql_query.lastInsertId().toInt();
        }

		dataBase.close();
        return f_success;
    }
    else
    {
        LOG_ERROR("try to save one sys_monitor_log failed,error:%s", QStr2CStr(sql_query.lastError().text()));
		dataBase.close();
        return f_fail;
    }

}

int DBService::deleteSysMonitorLogs(QString *kind, QString *timeStart, QString *timeEnd, bool* isSuccess)
{
    QString sql = "delete from sys_monitor_log where 1=1";
    if(kind) sql += " and kind = '" + *kind + "'";
    if(timeStart) sql += " and time >= '" + *timeStart + "'";
    if(timeEnd) sql += " and time <= '" + *timeEnd + "'";
    if(isSuccess != NULL) sql += " and is_success = " + int2QStr(boolTo0Or1(*isSuccess));

    return execSql(sql);
}

int DBService::loadSysMonitorLogsCount(int &count, QString *kind, QString *timeStart, QString *timeEnd, bool* isSuccess)
{
    QString sql = "select count(1) from sys_monitor_log where 1=1";
    if(kind) sql += " and kind = '" + *kind + "'";
    if(timeStart) sql += " and time >= '" + *timeStart + "'";
    if(timeEnd) sql += " and time <= '" + *timeEnd + "'";
    if(isSuccess != NULL) sql += " and is_success = " + int2QStr(boolTo0Or1(*isSuccess));


    return queryForInt(sql, count);
}

int DBService::doUpdateDBFrom5_0To6_0()
{
    LOG_INFO("doUpdateDBFrom5_0To6_0");
    int dbVersion = 0;
    if(getDBVersion(dbVersion) == f_success)
    {
        if(dbVersion < 5)
        {
            doUpdateDBFrom4_0To5_0();
        }

        QString sql = "alter table app_monitor add max_mem_kb INTEGER default -1";

        if(execSql(sql) == f_success)
        {
            LOG_INFO("try to create talbe sys_monitor_log success");

        }
        else
        {
            LOG_ERROR("try to create table sys_monitor_log failed");
            return f_fail;
        }

        return saveDBVersion(6);


    }else
    {
        LOG_ERROR("try to get db version failed, do doUpdateDBFrom4_0To5_0 failed");
        return f_fail;
    }

}

int DBService::doUpdateDBFrom4_0To5_0()
{
    LOG_INFO("doUpdateDBFrom4_0To5_0");
    int dbVersion = 0;
    if(getDBVersion(dbVersion) == f_success)
    {
        if(dbVersion < 4)
        {
            doUpdateDBFrom3_0To4_0();
        }

        QString sql = "CREATE TABLE  IF NOT EXISTS  sys_monitor_log (\
                 'id' INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,\
                 'kind' VARCHAR(150)  NOT NULL,\
                 'action' VARCHAR(100)  NOT NULL,\
                 'time' VARCHAR(60)  NOT NULL,\
                 'is_success' INTEGER default 0,\
                 'fail_reason' VARCHAR(100)\
                );";

        if(execSql(sql) == f_success)
        {
            LOG_INFO("try to create talbe sys_monitor_log success");

        }
        else
        {
            LOG_ERROR("try to create table sys_monitor_log failed");
            return f_fail;
        }

        return saveDBVersion(5);

    }else
        {
            LOG_ERROR("try to get db version failed, do doUpdateDBFrom4_0To5_0 failed");
            return f_fail;
        }
}

int DBService::doUpdateDBFrom3_0To4_0()
{
    LOG_INFO("doUpdateDBFrom3_0To4_0");
    int dbVersion = 0;
    if(getDBVersion(dbVersion) == f_success)
    {
        if(dbVersion < 3)
        {
            doUpdateDBFrom2_0To3_0();
        }

        {
            QString sql = "alter table app_monitor_log add is_success INTEGER default 0";

            if(execSql(sql) == f_success)
            {
                LOG_INFO("alter table app_monitor_log add is_success INTEGER default 0 success");
            }
            else
            {
                LOG_ERROR("alter table app_monitor_log add is_success INTEGER default 0 failed");
                return f_fail;
            }

            sql = "alter table app_monitor_log add fail_reason varchar(100) default ''";
            if(execSql(sql) == f_success)
            {
                LOG_INFO("alter table app_monitor_log add  fail_reason varchar(100) default '' success");
            }
            else
            {
                LOG_ERROR("alter table app_monitor_log add fail_reason varchar(100) default '' failed");
                return f_fail;
            }
        }
        return saveDBVersion(4);
    }
    else
    {
        LOG_ERROR("try to get db version failed, do doUpdateDBFrom3_0To4_0 failed");
        return f_fail;
    }
}

int DBService::doUpdateDBFrom2_0To3_0()
{
    LOG_INFO("doUpdateDBFrom2_0To3_0");
    int dbVersion = 0;
    if(getDBVersion(dbVersion) == f_success)
    {
        if(dbVersion < 2)
        {
            doUpdateDBFrom1_0To2_0();
        }

        {
            QString sql = "alter table app_monitor add stop INTEGER default 0";

            if(execSql(sql) == f_success)
            {
                LOG_INFO("alter table app_monitor add del INTEGER default 0 success");

            }
            else
            {
                LOG_ERROR("alter table app_monitor add del INTEGER default 0 failed");
                return f_fail;
            }
        }
        return saveDBVersion(3);
    }
    else
    {
        LOG_ERROR("try to get db version failed, do doUpdateDBFrom2_0To3_0 failed");
        return f_fail;
    }
}
int DBService::doUpdateDBFrom1_0To2_0()
{
    LOG_INFO("doUpdateDBFrom1_0To2_0");
    int dbVersion = 0;
    if(getDBVersion(dbVersion) == f_success)
    {
        if(dbVersion < 1)
        {
            doUpdateDBFrom0_0To1_0();
        }

        {
            QString sql = "CREATE TABLE  IF NOT EXISTS  app_monitor_log (\
                     'id' INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,\
                     'name' VARCHAR(150)  NOT NULL,\
                     'action' VARCHAR(100)  NOT NULL,\
                     'time' VARCHAR(60)  NOT NULL\
                    );";

            if(execSql(sql) == f_success)
            {
                LOG_INFO("try to create talbe app_monitor_log success");

            }
            else
            {
                LOG_ERROR("try to create table app_monitor_log failed");
                return f_fail;
            }
        }
        return saveDBVersion(2);
    }
    else
    {
        LOG_ERROR("try to get db version failed, do doUpdateDBFrom1_0To2_0 failed");
        return f_fail;
    }
}

int DBService::doUpdateDBFrom0_0To1_0()
{
    LOG_INFO("doUpdateDBFrom0_0To1_0");
    int dbVersion = 0;
    if(getDBVersion(dbVersion) == f_success)
    {
        if(dbVersion == 0)
        {
            QString sql = "CREATE TABLE  IF NOT EXISTS  app_monitor (\
                     'id' INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,\
                     'name' VARCHAR(150)  NOT NULL,\
                     'type' INTEGER  NOT NULL,\
                     'start_cmd' VARCHAR(200)  NOT NULL,\
                     'cfg_info' VARCHAR(150)  NOT NULL,\
                     'interval' INTEGER DEFAULT '2' NOT NULL\
                    );";
            if(execSql(sql) == f_success)
            {
                LOG_INFO("try to create talbe app_monitor success");

            }
            else
            {
                LOG_ERROR("try to create table app_monitor failed");
                return f_fail;
            }

            return saveDBVersion(1);
        }
        else
        {
            LOG_ERROR("only support update db from 0 to 1.0 in this function");
            return f_fail;
        }
    }
    else
    {
        LOG_ERROR("try to get db version failed, do doUpdateDBFrom0_0To1_0 failed");
        return f_fail;
    }
}

int DBService::createSysSettingTable()
{
    QString sql = "CREATE TABLE IF NOT EXISTS " + SYS_SETTING_TABLE_NAME + "(key varchar(100) not null, value varchar(200));";

    return execSql(sql);
}

int DBService::getDBVersion(int & version)
{
    QString versionStr;
    if(getSysSettingValue(DB_VERSION_KEY, versionStr) == f_success)
    {
        version = versionStr.toInt();
        return f_success;
    }
    else
    {
        return f_fail;
    }
}

int DBService::saveDBVersion(int version)
{

    if(deleteSysSettingValue(DB_VERSION_KEY) == f_success && insertSysSettingValue(DB_VERSION_KEY, int2QStr(version)) == f_success)
    {
        return f_success;
    }
    else
    {
        return f_fail;
    }
}

int DBService::isTableExist(const QString& tableName, bool &exist)
{

    QString sql = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name= '" + tableName + "'";
    int value = 0;
    if(queryForInt(sql, value) == f_success)
    {
        exist = value > 0;
        return f_success;
    }
    else
    {
        LOG_ERROR("try to check if talbe:%s exist with sql:%s failed", QStr2CStr(tableName), QStr2CStr(sql));
        return f_fail;
    }


}

int DBService::queryForInt(const QString &sql, int &value)
{
    int iRet = f_fail;


    QSqlDatabase dataBase;
    openDB(dataBase);
    QSqlQuery sql_query(dataBase);
	
	
    sql_query.prepare(sql);
    if(!sql_query.exec())
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
    }
    else
    {
        while(sql_query.next())
        {
            iRet = f_success;
            value = sql_query.value(0).toInt();
            break;
        }

        if(iRet == f_success)
        {
            LOG_INFO("get int value:%d, from sql:%s", value, QStr2CStr(sql));
        }
        else
        {
            LOG_ERROR("faild to get int valie from sql:%s, sql result is empty!", QStr2CStr(sql));
        }
    }

    dataBase.close();
    return iRet;

}

int DBService::queryForString(const QString  & sql, QString &value)
{
    int iRet = f_fail;
    QSqlDatabase dataBase;
    openDB(dataBase);
    QSqlQuery sql_query(dataBase);
    sql_query.prepare(sql);
    if(!sql_query.exec())
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
    }
    else
    {
        while(sql_query.next())
        {
            iRet = f_success;
            value = sql_query.value(0).toString();
            break;
        }

        if(iRet == f_success)
        {
            LOG_INFO("get string value:%s, from sql:%s", QStr2CStr(value), QStr2CStr(sql));
        }
        else
        {
            LOG_WARN("faild to get string valie from sql:%s, sql result is empty!", QStr2CStr(sql));
        }
    }
    dataBase.close();
    return iRet;
}

int DBService::execSql(const QString & sql)
{
    int iRet = f_fail;
    QSqlDatabase dataBase;
    openDB(dataBase);
    QSqlQuery sql_query(dataBase);
    if(!sql_query.exec(sql))
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
    }
    else
    {
        iRet = f_success;
        LOG_INFO("exec sql:%s success", QStr2CStr(sql));
    }
    dataBase.close();

    return iRet;

}
